from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from threading import Thread
from my_frame.shoppeQK import DownLog
from xpinyin import Pinyin
from sql import sqlseverDB
from mini_tools.myexcel import *
from mini_tools.mytools import *
import tkinter.messagebox as messagebox
import openpyxl,re,tkinter,copy


class MiniFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        username=get_config('Uname')
        if username=='xiahongfei':
            DownLog(self).pack(fill=X,padx=2,pady=2)
        Xgn(self).pack(fill=X,padx=3,pady=3)
        QzFgFra(self).pack(fill=X,padx=3,pady=3)
        
def getpy(mes):
    p=Pinyin()
    return p.get_pinyin(mes,splitter='',convert='capitalize')

def getDictDatasByEecel(ws):
    datas=[]    
    headers=[getpy(ws.cell(1,col).value) for col in range(1,ws.max_column+1)]
    for row in range(2,ws.max_row+1):

        currow={}
        for col in range(1,ws.max_column+1):
            curtitle=headers[col-1]
            currow[curtitle]=ws.cell(row,col).value
        datas.append(currow)

    return datas

class Xgn(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.createWidgets()

    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='截取标题', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f00=Frame(f0)
        f00.grid(row=1,column=1,pady=5,padx=5,sticky='nw',rowspan=3)
        Label(f00, text='截取字符数:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        self.autotc()
        Button(f00,text='截取',command=self.createTitle).pack(side=LEFT,padx=2, pady=2)

        f02=Frame(f0)
        f02.grid(row=1,column=2,rowspan=3,padx=10,sticky='nw')
        Label(f02, text='输入标题:').grid(row=1,column=1,sticky='nw')
        self.titTxt=Text(f02,width=60,height=10)
        self.titTxt.grid(row=2,column=1,sticky='nw')
    
    def autotc(self):
        znum=get_config('JieQuBT')
        if znum:
            self.DaoRuIpt.insert(0,znum)


    def createTitle(self):
        
        znum= int(self.DaoRuIpt.get().strip())
        update_config({'JieQuBT':znum})
        ctxt=self.titTxt.get(1.0, END)

        self.titTxt.delete(1.0,END)

        self.titTxt.insert(END,ctxt[:znum])


class QzFgFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        # self.pack(fill=X,padx=3,pady=3)
        self.statustxt=StringVar()
        self.statustxt.set(f'未导入表格')
        self.statustxt2=StringVar()
        self.statustxt2.set(f'未导入表格')
        self.createWidgets()
    def createWidgets(self):

        ttk.Separator(self,orient='horizontal').pack(fill=X,pady=5)
        Label(self, text='EXCEL处理', fg="black", bg="yellow",font=("微软雅黑", 14)).pack(fill=X,padx=5)

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)

        f00=Frame(f0)
        f00.grid(row=1,column=1,sticky='nw',padx=5)
        ttk.Separator(f0,orient='vertical').grid(row=1,column=2,sticky='nw',padx=50)
        f01=Frame(f0)
        f01.grid(row=1,column=3,sticky='nw',ipadx=30)
        
        Label(f00, text='文档处理', fg="black", bg="#3299CC",font=("微软雅黑", 12)).pack(fill=X,padx=5,pady=2)

        f000=Frame(f00)
        f000.pack(pady=5,fill=X,padx=5)
        Label(f000, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f000)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f000,text='选择文件',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)

        f002=Frame(f00)
        f002.pack(fill=X,padx=5)
        Label(f002, text='导入目录:').pack(side=LEFT, padx=2)
        self.DrmrIpt = Entry(f002)
        self.DrmrIpt.pack(side=LEFT, padx=2)
        Button(f002,text='选择目录',command=self.chooseDir).pack(side=LEFT,padx=2)

        f001=Frame(f00)
        f001.pack(pady=5,fill=X,padx=5)
        Label(f001, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)
        btnFra1=Frame(f00)
        btnFra1.pack(pady=5,fill=X,padx=5)
        Button(btnFra1,text='去重',command=self.qzec).pack(side=LEFT,fill=X,padx=5)
        Button(btnFra1,text='统一数字类型',command=self.tongyi).pack(side=LEFT,fill=X,padx=5)


        Label(f01, text='分割excel', fg="black", bg="#3299CC",font=("微软雅黑", 12)).pack(fill=X,padx=5,pady=2)
        f010=Frame(f01)
        f010.pack(pady=5,fill=X,padx=5)
        f014=Frame(f01)
        f014.pack(fill=X,padx=5)
        f015=Frame(f01)
        f015.pack(fill=X,padx=5)
        f011=Frame(f01)
        f011.pack(pady=5,fill=X,padx=5)
        f012=Frame(f01)
        f012.pack(pady=5,fill=X,padx=5)
        f013=Frame(f01)
        f013.pack(pady=5,fill=X,padx=5)

        Label(f010, text='导入excel:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt2 = Entry(f010)
        self.DaoRuIpt2.pack(side=LEFT, padx=2, pady=2)
        Button(f010,text='选择文件',command=self.choosefile2).pack(side=LEFT,padx=2, pady=2)

        Label(f014, text='导入目录:').pack(side=LEFT, padx=2)
        self.DrmrIpt2 = Entry(f014)
        self.DrmrIpt2.pack(side=LEFT, padx=2)
        Button(f014,text='选择目录',command=self.chooseDir2).pack(side=LEFT,padx=2)

        Label(f015, textvariable=self.statustxt2,fg='red').pack(side=LEFT, padx=2)

        Label(f012, text='分割行数:').pack(side=LEFT, padx=2, pady=2)
        self.searchKwIpt = Entry(f012)
        self.searchKwIpt.insert(0,500)
        self.searchKwIpt.pack(side=LEFT, padx=2, pady=2)

        Button(f013,text='分割',command=self.fgec,fg='#ffffff',bg='green',width=20).grid(row=1,column=1,sticky='nw',padx=10,pady=2)
        Button(f013,text='合并',command=self.hb,fg='#ffffff',bg='green',width=20).grid(row=1,column=2,sticky='nw',padx=10,pady=2)

    
    def choosefile(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def choosefile2(self):

        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt2.delete(0, END)
            self.DaoRuIpt2.insert(0,select_path)
            self.fpath2=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt2.set(f'当前表格共{max_row}行')
        else:
            messagebox.showerror('错误提示','请选择excel文件')

    def chooseDir(self):
        import os
        select_path=filedialog.askdirectory()
        self.DrmrIpt.delete(0, END)
        self.DrmrIpt.insert(0,select_path)
        fs=os.listdir(select_path)
        ecls=[select_path+r'/'+f for f in fs if f.endswith('.xlsx')]
        if len(ecls)==0:
            messagebox.showinfo('提示','当前目录下无excel文件')
            self.DrmrIpt.delete(0, END)
            return
        self.ecls=ecls
    
    def chooseDir2(self):
        import os
        select_path=filedialog.askdirectory()
        self.DrmrIpt2.delete(0, END)
        self.DrmrIpt2.insert(0,select_path)
        self.dirpath2=select_path
        fs=os.listdir(select_path)
        ecls=[select_path+r'/'+f for f in fs if f.endswith('.xlsx')]
        if len(ecls)==0:
            messagebox.showinfo('提示','当前目录下无excel文件')
            self.DrmrIpt2.delete(0, END)
            return
        self.ecls2=ecls

    def qzec(self):
        if not self.DaoRuIpt.get():
            return
        delduplicates(self.fpath)
        print('去重完毕')
        messagebox.showinfo('提示','操作完毕')
    
    def hb(self):
        if not self.DrmrIpt2.get():
            return
        print(self.ecls2,self.dirpath2)
        hebing(self.ecls2,self.dirpath2)
        print('合并完毕')
        messagebox.showinfo('提示','操作完毕')

    def tongyi(self):

        if not self.DrmrIpt.get().strip():
            return
        Shuzitongyi(self.ecls)
        messagebox.showinfo('提示','操作完毕')
        
    def fgec(self):
        if not self.DaoRuIpt2.get():
            return
        qiepian(self.fpath2,int(self.searchKwIpt.get()))
        print('分割完毕')
        messagebox.showinfo('提示','操作完毕')

# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('小工具')
# MiniFra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()

